package com.heying.springcore.aop.jdbc.repository.jdbc;

import com.heying.springcore.aop.jdbc.entity.Books;
import com.heying.springcore.aop.jdbc.repository.BookRepository;
import org.springframework.jdbc.core.JdbcTemplate;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

public class JdbcBookRepository implements BookRepository {

    private JdbcTemplate jdbcTemplate;

    public JdbcBookRepository(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    @Override
    public List<Books> queryBooksByCustomer(String customer) {
        return jdbcTemplate.query("SELECT b.* FROM tbl_books b " +
                "RIGHT JOIN tbl_orderitems oi ON b.ISBN=oi.ISBN " +
                "RIGHT JOIN tbl_orders o ON oi.ORDERNO=o.ORDERNO " +
                "RIGHT JOIN tbl_customer c ON o.CUSTOMER=c.CUSTOMER " +
                "WHERE c.CUSTOMER=?",this::mapToBooks);
    }
    public Books mapToBooks(ResultSet rs, int row) throws SQLException {
        return new Books(rs.getString("isbn"),
                rs.getString("title"),
                rs.getDate("date"),
                rs.getInt("pubId"),
                rs.getDouble("cost"),
                rs.getDouble("retail"),
                rs.getString("category"));
    }
}
